package test.com;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Hello world!
 *
 */
public class Shopapp 
{
    public static Connection getConn() {
		String driver = "com.mysql.cj.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/train?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8";
		String username = "root";
		String password = "";
		Connection conn = null;
		try {
			Class.forName(driver); // classLoader,加载对应驱动
			conn = (Connection) DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static int insertShop(Shop shop) {
		Connection conn = getConn();
		int i = 0;
		String sql = "insert into shop (ShopName, CoffeeName, CoffeeRating, ShopRating) values (?, ?, ?, ?)";
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setString(1, shop.getShopName());
			pstmt.setString(2, shop.getCoffeeName());
            pstmt.setInt(3, shop.getCoffeeRating());
            pstmt.setInt(4, shop.getShopRating());
			i = pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	public static boolean existShop(Shop shop) {
		boolean exist = false;
		Connection conn = getConn();
		String sql = "select * from shop";
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			//pstmt.setString(1, shop.getUrl());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				exist = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return exist;
    }
    
    public static List<Shop> getShops() {
		List<Shop> shops = new ArrayList<Shop>();
		Connection conn = getConn();
		String sql = "select * from shop";		
		try {
            Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
                Shop shop = new Shop();
                shop.setShopName(rs.getString("ShopName"));
                shop.setCoffeeName(rs.getString("CoffeeName"));
                shop.setCoffeeRating(rs.getInt("CoffeeRating"));
                shop.setShopRating(rs.getInt("ShopRating"));
				shops.add(shop);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return shops;
	}

	public static void main(String[] args) {
        Shop shop = new Shop();
        shop.setShopName("Starbuck");
        shop.setCoffeeName("Americano");
        shop.setCoffeeRating(3);
        shop.setShopRating(3);
		try {		
			if (!Shopapp.existShop(shop)) {
				Shopapp.insertShop(shop);
            }
            List<Shop> shops = Shopapp.getShops();
            System.out.print(shops);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
